package sueldos;

import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.itextpdf.text.DocumentException;
import com.itextpdf.text.pdf.AcroFields;
import com.itextpdf.text.pdf.PdfReader;
import com.itextpdf.text.pdf.PdfStamper;


public class AltasSeguroRurales extends Empleado{


	
	
    		
    public static void main(String[] args) throws IOException, DocumentException {
    		
    	//	for (int i = 1; i < 80; i++) {
		int emp = 4;
		Empresa e = new Empresa();
		e.getDataDB(emp);
		String empresa = e.getBase();
		
		//String empresa = "clavier";
    	//int[] legajos = {86,87}; 
		
    	
    	
    	try {
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+empresa, "", ""); 
			String query="SELECT empleado.legajo, CONCAT(empleado.apellido,' ',empleado.nombres) AS apenom, " +
					"	TIPODOCU, NRODOCU, 	1 AS ocupacion, DAY(FECHANACI) AS diaNac, MONTH(FECHANACI) AS mesNac, " +
					"	DATE_FORMAT(FECHANACI, '%y')  AS yearNac, 	DAY(FECHAING) AS diaIngreso, MONTH(FECHAING) AS mesIngreso," +
					"	DATE_FORMAT(FECHAING, '%y') AS yearIngreso, SUM((ROUND(REM_APORTE,2))) AS sueldo " +
					"FROM empleado 	INNER JOIN liquidac  ON (`empleado`.`LEGAJO` = `liquidac`.`LEGAJO`) " +
					"WHERE empleado.legajo in (86)  and liquidac.periodo =  '2015-04-02' AND FECHAEGR IS NULL " +
					"GROUP BY empleado.legajo ORDER BY empleado.LEGAJO";
			PreparedStatement ps = conexion.prepareStatement(query);
			//ps.setLong(1, 1);
			ps.execute();
			ResultSet rs = ps.getResultSet();
  		
			String filename = e.getNombre()+" Alta Beneficiarios Poliza "+e.getPolizaSeguro();
    		//String filename = "Altas Seguro "+empresa;
    		String output = "C:/Users/Juani/Desktop/"+filename+".pdf";
    		
    		PdfReader reader = new PdfReader("E:/167 - La Caja/00 -Empleados Altas - La Caja - Vigente.pdf");
            PdfStamper filledOutForm = new PdfStamper(reader, new FileOutputStream(output));
            
            AcroFields form = filledOutForm.getAcroFields();

            form.setField("tomador", e.getNombre());
            form.setField("calle", "Uruguay 911 2 A");
            form.setField("localidad", "CABA");
            form.setField("poliza", e.getPolizaSeguro());
 
            int i = 0;
            
            //for (int i = 0; i < 21; i++) {
            while (rs.next() && i < 21)	{
              	 form.setField("apenom["+i+"]", rs.getString(2));
				 form.setField("tipodoc["+i+"]", rs.getString(3));
				 form.setField("nrodoc["+i+"]", rs.getString(4));
				 form.setField("ocupacion["+i+"]", rs.getString(5));
				 form.setField("diaNac["+i+"]", rs.getString(6));
				 form.setField("mesNac["+i+"]", rs.getString(7));
				 form.setField("yearNac["+i+"]", rs.getString(8));
				 form.setField("diaIngreso["+i+"]", rs.getString(9));
				 form.setField("mesIngreso["+i+"]", rs.getString(10));
				 form.setField("yearIngreso["+i+"]", rs.getString(11));
				 form.setField("sueldoBruto["+i+"]", rs.getString(12));
            	i++;
 			}
            
            filledOutForm.setFormFlattening(true);
            filledOutForm.close();
            Desktop.getDesktop().open(new File(output));

		} catch (Exception error) {
			error.printStackTrace();
		}

    }
}




